﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using AG.Utilities;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class ServiceCheckNotLatestServicePack :IssueTypeCheckBase
    {

        public ServiceCheckNotLatestServicePack()
        {
            IssueTypeId = 2001;
        }

        public override void Check()
        {

            try
            {
                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
                Issue issue;
                //Query database (SERVICE TABLE)for service packs installed on each db 

                String maxSP2005 = dc.SQLServerVersions.Where(s => s.Version.StartsWith("9.0")).Max(s => s.Version);
                String maxSP2008 = dc.SQLServerVersions.Where(s => s.Version.StartsWith("10.0")).Max(s => s.Version);
                String maxSP2008R2 = dc.SQLServerVersions.Where(s => s.Version.StartsWith("10.5")).Max(s => s.Version);
                String maxSP2012 = dc.SQLServerVersions.Where(s => s.Version.StartsWith("11.0")).Max(s => s.Version);



                var ServiceQry = from s in dc.Services
                                 where s.ProductVersion != null && s.ServiceTypeID==1
                                select s;

                foreach (
                            var service 
                            in 
                                ServiceQry.ToList<Service>().Where(s=>
                                (s.ProductVersion.StartsWith("9.0") && (ServiceCheckNotLatestServicePack.GetFirstDigitSP(s.ProductVersion).CompareTo(ServiceCheckNotLatestServicePack.GetFirstDigitSP(maxSP2005)) < 0)
                                 ||

                                  (s.ProductVersion.StartsWith("10.0")) && (ServiceCheckNotLatestServicePack.GetFirstDigitSP(s.ProductVersion).CompareTo(ServiceCheckNotLatestServicePack.GetFirstDigitSP(maxSP2008)) < 0)

                                   ||

                                   (s.ProductVersion.StartsWith("10.5")) && (ServiceCheckNotLatestServicePack.GetFirstDigitSP(s.ProductVersion).CompareTo(ServiceCheckNotLatestServicePack.GetFirstDigitSP(maxSP2008R2)) < 0)

                                 ||

                                   (s.ProductVersion.StartsWith("11")) && (ServiceCheckNotLatestServicePack.GetFirstDigitSP(s.ProductVersion).CompareTo(ServiceCheckNotLatestServicePack.GetFirstDigitSP(maxSP2012)) < 0)))
                                   
                        )
                {
                    string latestSP = "";
                    if (service.ProductVersion.StartsWith("9.0"))
                    {
                        latestSP = maxSP2005;

                    }
                    else if (service.ProductVersion.StartsWith("10.0"))
                    {

                        latestSP = maxSP2008;
                    }
                    else if (service.ProductVersion.StartsWith("10.5"))
                    {

                        latestSP = maxSP2008R2;
                    }
                    else if (service.ProductVersion.StartsWith("11"))
                    {

                        latestSP = maxSP2012;
                    }

                    issue = new Issue();


                    IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);

                    SQLServerVersion version = dc.SQLServerVersions.FirstOrDefault(sv => sv.Version == latestSP);
                    String spDescription = "";
                    if (version != null)
                    {
                        spDescription = version.Description;
                    }

                    issue.IssueType = issueType;
                    issue.TargetId = service.ServiceID;
                    issue.Description = issueType.Description.Replace("{InstanceName}", service.InstanceName).Replace("{oldSP}", service.ProductVersion).Replace("{newSP}", latestSP).Replace("{DescriptionSP}", spDescription);

                    dc.Issues.InsertOnSubmit(issue);
                }

                dc.SubmitChanges();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }


        public static string GetFirstDigitSP(string version)
        {
            string[] numbers = version.Split('.');

            return numbers[2].Substring(0,1);

        }


    }
       
}
